package DBLayer;

import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Product;
import java.sql.*;


public class DBProduct implements IFDBProduct {
	private Connection con;
	//creates a new instance of DBProduct
	public DBProduct(){
		con = DbConnection.getInstance().getDBcon();
	}
	//Implements the methods from the interface
	//get all products
	public ArrayList<Product> getAllProducts(boolean retriveAssociation){
		return miscWhere("", retriveAssociation);
	}
	//get Product 
    public Product findProduct(int productID, boolean retriveAssociation){
    	String wClause = "  productID = '" + productID + "'";
        return singleWhere(wClause, retriveAssociation);
    }
	// find one product having the "name"
    public Product searchProductName(String attValue, boolean retriveAssociation){
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    //insert a new Product
    public int insertProduct(Product pro) throws Exception{
    	//call to get the next ProductID
        int nextId = GetMax.getMaxId("Select max(productID) from Product");
        nextId = nextId + 1;
        System.out.println("next productID = " +  nextId);
  
       int rc = -1;
       
       //INSERT 
	   String query="INSERT INTO Product(productID, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, size, colour, pType, pDescription, fabric, calibre)  VALUES('"+
	  		pro.getProductID()  + "','"  +
			pro.getName()  + "','"  +
		    pro.getPurchasePrice()   + "','"  +
            pro.getSalesPrice()   + "','"  +
		    pro.getRentPrice()   + "','"  +
            pro.getCountryOfOrigin()   + "','"  +
		    pro.getMinStock()   + "','"  +
            pro.getSize()   + "','"  +
		    pro.getColour()   + "','"  +
            pro.getpType()   + "','"  +
		    pro.getpDescription()   + "','"  +
            pro.getFabric()   + "','"  +
		    pro.getCalibre()   + "','"  +
		    nextId + "','" + "";
            
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Product ikke oprettet");
          throw new Exception ("Product is not inserted correct");
       }
       return(rc);
    }
    // updates Product
	public int updateProduct(Product pro){
		Product proObj  = pro;
		int rc=-1;

		String query="UPDATE Product SET "+
		 	  "productID ='"+ proObj.getProductID()+"', "+
		 	  "name ='"+ proObj.getName()+ "', " +
                          "purchasePrice ='"+ proObj.getPurchasePrice() + "', " +
                          "salesPrice ='"+ proObj.getSalesPrice() + "' " +
                          "rentPrice ='"+ proObj.getRentPrice() + "' " +
                          "countryOfOrigin ='"+ proObj.getCountryOfOrigin() + "' " +
                          "minStock ='"+ proObj.getMinStock() + "' " +
                          "size ='"+ proObj.getSize() + "' " +
                          "colour ='"+ proObj.getColour() + "' " +
                          "pType ='"+ proObj.getpType() + "' " +
                          "pDescription ='"+ proObj.getpDescription() + "' " +
                          "fabric ='"+ proObj.getFabric() + "' " +
		          " calibre = '"+ proObj.getCalibre() + "'";
                System.out.println("Update query:" + query);
  		try{ // update Product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
	public int delete(String productID){
               int rc=-1;
	  
	  	String query="DELETE FROM product WHERE productID = '" +
				productID + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one product
	
	 
	private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the Product from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			Product proObj = new Product();
		 proObj = buildProduct(results);	
                 list.add(proObj);	
		}//end while
                 stmt.close();     
//                 if(retrieveAssociation)
//                 {   //The supervisor and department is to be build as well
//                     for(Product cuObj : list){
//                        String superssn = cuObj.getSupervisor().getSsn();
//                        Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                        empObj.setSupervisor(superEmp);
//                        System.out.println("test test");
//                        DBDepartment departObj = new DBDepartment();
//                        Department dep = departObj.findDepartment(empObj.getDept().getDnumber(), false);
//                        empObj.setDepartment(dep);
//                     }
//                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Product proObj = new Product();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            proObj = buildProduct(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {   //The supervisor and department is to be build as well
//                                String superssn = empObj.getSupervisor().getSsn();
//                                Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                                empObj.setSupervisor(superEmp);
//                                System.out.println("test2 tes2");
//                                DBDepartment departObj = new DBDepartment();
//                                Department dep = departObj.findDepartment(empObj.getDept().getDnumber(), false);
//                                empObj.setDepartment(dep);
//                           
//                           
//                            }
//			}
//                        else{ //no employee was found
//                            cuObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return proObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT productID, name, cAddress, zipcode, city, phoneNo From Product ";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an employee object
	private Product buildProduct(ResultSet results)
      {   Product proObj = new Product();
          try{ // the columns from the table Product  are used
               proObj.setProductID(results.getInt("productID"));
                proObj.setName(results.getString("name"));
                proObj.setPurchasePrice(results.getDouble("purchasePrice"));
                proObj.setSalesPrice(results.getDouble("salesPrice"));
                proObj.setRentPrice(results.getDouble("rentPrice"));
                proObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
                proObj.setMinStock(results.getInt("minStock"));
                proObj.setSize(results.getInt("size"));
                proObj.setColour(results.getString("colour"));
                proObj.setpType(results.getString("pType"));
                proObj.setpDescription(results.getString("pDescription"));
                proObj.setFabric(results.getString("fabric"));
                proObj.setCalibre(results.getString("calibre"));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the product object");
         }
         return proObj;
      }
	@Override
	public int Product(ModelLayer.Product pro) {
		// TODO Auto-generated method stub
		return 0;
	}
	@Override
	public ModelLayer.Product searchProductID(int productID, boolean b) {
		// TODO Auto-generated method stub
		return null;
	}
	


 
}  
    



